﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace Clmp.Data.DBEntity2
{
    public class DataContainer : IDataContainer
    {
        public DataContainer(IDbClientFactory clientFactory, IDbEntityServer dbEntityServer)
        {
            DbClientFactory = clientFactory;
            DbConnection = DbClientFactory.GetDbConnection();
            DbEntityServer = dbEntityServer;
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return DbConnection.ConnectionString; }
            set { DbConnection.ConnectionString = value; }
        }
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        protected IDbConnection DbConnection { get; private set; }
        protected IDbClientFactory DbClientFactory { get; private set; }
        protected IDbEntityServer DbEntityServer { get; set; }

        #region IDataContainer 成员
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>自身</returns>
        public IDataContainer OpenConnection()
        {
            DbConnection.Open();
            return this;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns>自身</returns>
        public IDataContainer CloseConnection()
        {
            DbConnection.Close();
            return this;
        }
        /// <summary>
        /// 开启数据库事物
        /// </summary>
        /// <returns>事物处理对象</returns>
        public IDbTransaction BeginTransaction()
        {
            return DbConnection.BeginTransaction();
        }

        #region 实体增删改获取
        /// <summary>
        /// 插入一个实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <returns>执行Sql语句影响的行数</returns>
        public int Insert<T>(T entity) where T : Entity
        {
            var parDic = entity.GetPropertyDictionary();
            string sqlStr = DbEntityServer.GetInsertSqlString(entity);
            var command = CreateCommand(sqlStr, parDic);
            return command.ExecuteNonQuery();
        }
        /// <summary>
        /// 更新一个实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <returns>执行Sql语句影响的行数</returns>
        public int Update<T>(T entity) where T : Entity
        {
            var parDic = entity.GetPropertyDictionary();
            string sqlStr = DbEntityServer.GetUpdateSqlString(entity);
            var command = CreateCommand(sqlStr, parDic);
            return command.ExecuteNonQuery();
        }
        /// <summary>
        /// 删除一个实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">实体ID</param>
        /// <returns>执行Sql语句影响的行数</returns>
        public int Delete<T>(int id) where T : Entity
        {
            string sqlStr = DbEntityServer.GetDeleteSqlString<T>(id);
            var command = CreateCommand(sqlStr, new object[] {id});
            return command.ExecuteNonQuery();
        }
        /// <summary>
        /// 获取一个实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">实体ID</param>
        /// <returns>实体对象</returns>
        public T GetOne<T>(int id) where T : Entity
        {
            string sqlStr = DbEntityServer.GetGetOneSqlString<T>(id);
            var command = CreateCommand(sqlStr, new object[] { id });
            using (var reader = command.ExecuteReader())
            {
                if (reader != null)
                    if (reader.Read())
                    {
                        T model = EntityFactory<T>.CreateEntity();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!reader.IsDBNull(i))
                            {
                                var value = reader.GetValue(i);
                                model.SetPropertyValue(reader.GetName(i), value);
                            }
                        }
                        return model;
                    }
            }
            return null;
        }
        #endregion 实体增删改获取

        #region 执行Sql语句
        /// <summary>
        /// 执行数据库查询
        /// </summary>
        /// <typeparam name="T">查询返回的实体类型</typeparam>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>实体集合</returns>
        public List<T> Query<T>(string sqlStr, params object[] pars) where T : Entity
        {
            var command = CreateCommand(sqlStr, pars);
            List<T> entitys = new List<T>();
            using (var reader = command.ExecuteReader())
            {
                if (reader != null)
                    while (reader.Read())
                    {
                        T model = EntityFactory<T>.CreateEntity();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!reader.IsDBNull(i))
                                model.SetPropertyValue(reader.GetName(i), reader.GetValue(i));
                        }
                        entitys.Add(model);
                    }
            }
            GetOutputValue(command, pars);
            return entitys;
        }
        /// <summary>
        /// 执行Sql语句，返回影响的行数
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>执行语句影响的行数</returns>
        public int ExecuteNonQuery(string sqlStr, params object[] pars)
        {
            var command = CreateCommand(sqlStr, pars);
            var rtn = command.ExecuteNonQuery();
            GetOutputValue(command, pars);
            return rtn;
        }
        /// <summary>
        /// 执行Sql语句，返回查询结果第一行第一列的值
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>查询结果第一行第一列的值</returns>
        public object ExecuteScalar(string sqlStr, params object[] pars)
        {
            var command = CreateCommand(sqlStr, pars);
            var rtn = command.ExecuteScalar();
            GetOutputValue(command, pars);
            return rtn;
        }
        /// <summary>
        /// 执行Sql语句，返回查询结果第一行第一列的值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>查询结果第一行第一列的值</returns>
        public T ExecuteScalar<T>(string sqlStr, params object[] pars)
        {
            var rtnVal = ExecuteScalar(sqlStr, pars);
            if (rtnVal == null)
                return default(T);
            return (T) rtnVal;
        }
        /// <summary>
        /// 执行Sql语句，返回DataSet对象
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>查询结果</returns>
        public DataSet GetDateSet(string sqlStr, params object[] pars)
        {
            var command = CreateCommand(sqlStr, pars);
            var dbAdapter = DbClientFactory.GetDbDataAdapter();
            dbAdapter.SelectCommand = command;
            var dataSet = new DataSet();
            dbAdapter.Fill(dataSet);
            GetOutputValue(command, pars);
            return dataSet;
        }
        /// <summary>
        /// 执行Sql语句，返回DataReader对象
        /// </summary>
        /// <param name="sqlStr">Sql语句</param>
        /// <param name="pars">数据库参数</param>
        /// <returns>DataReader对象</returns>
        public IDataReader GetDataReader(string sqlStr, params object[] pars)
        {
            var command = CreateCommand(sqlStr, pars);
            var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            GetOutputValue(command, pars);
            return reader;
        }

        #endregion 执行Sql语句

        #endregion

        #region 私有方法
        private static readonly Regex ParRegex = new Regex("@(?<par>[\\w]+)");
        private IDbCommand CreateCommand(string sqlStr, Dictionary<string, object> parDic)
        {
            var command = DbConnection.CreateCommand();
            command.CommandText = sqlStr;

            MatchCollection matches = ParRegex.Matches(sqlStr);

            if (matches.Count != parDic.Count())
                throw new ArgumentException("Sql语句与提供的数据库参数不匹配。");

            foreach (Match t in matches)
            {
                string parName = t.Result("${par}");
                object parVal = parDic[parName];
                command.Parameters.Add(DbClientFactory.GetDataParameter(parName, parVal, ParameterDirection.Input));
            }
            return command;
        }

        private IDbCommand CreateCommand(string sqlStr, object[] pars)
        {
            bool isStoredProcedures = IsStoredProcedures(sqlStr);
            var command = DbConnection.CreateCommand();
            command.CommandText = sqlStr;

            if (!isStoredProcedures)
            {
                if (pars != null && pars.Length > 0)
                {
                    MatchCollection matches = ParRegex.Matches(sqlStr);

                    if (matches.Count != pars.Length)
                        throw new ArgumentException("Sql语句与提供的数据库参数不匹配。");

                    for (var i = 0; i < matches.Count; i++)
                    {
                        object parVal = pars[i];
                        string parName = matches[i].Result("${par}");
                        command.Parameters.Add(DbClientFactory.GetDataParameter(parName, parVal,
                                                                                ParameterDirection.Input));
                    }
                }
            }
            else
            {
                command.CommandType = CommandType.StoredProcedure;
                if (pars != null && pars.Length > 0)
                {
                    foreach (var par in pars)
                    {
                        if (par is IStoredProcedureParameter)
                        {
                            var spPar = (IStoredProcedureParameter) par;
                            command.Parameters.Add(DbClientFactory.GetDataParameter(spPar.Name, spPar.Value,
                                                                                    spPar.Direction));
                        }
                        else
                        {
                            throw new ArgumentException("存储过程参数必须是IStoredProcedureParameter类型。");
                        }
                    }
                }
            }
            return command;
        }

        private static void GetOutputValue(IDbCommand command,object[] pars)
        {
            foreach (var par in pars)
            {
                if (par is IStoredProcedureParameter)
                {
                    var spPar = (IStoredProcedureParameter)par;
                    if (spPar.Direction != ParameterDirection.Input)
                        spPar.Value = ((DbParameter)command.Parameters["@" + spPar.Name]).Value;
                }
            }
        }
        
        private static bool IsStoredProcedures(string sqlStr)
        {
            return Regex.IsMatch(sqlStr, "^\\w+$");
        }
        #endregion 私有方法

        #region IDisposable 成员

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion
    }
}
